﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using BLL;

namespace DAL.Managers
{
    public class SerieManager
    {
        #region properties

        private static SerieManager _sInstance;
        private static readonly object SInstanceLocker = new object();

        #endregion

        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>The instance.</value>
        public static SerieManager Instance
        {
            get
            {
                lock (SInstanceLocker)
                {
                    return _sInstance ?? (_sInstance = new SerieManager());
                }
            }
        }

        #region public functions

        /// <summary>
        /// Gets the number of books by serie.
        /// </summary>
        /// <param name="idSerie">The id of the serie.</param>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>The number of books in the serie.</returns>
        public int GetNumberBooksBySerie(Int64 idSerie, SQLiteConnection conn = null)
        {
            int nb;

            if (conn != null)
            {
                // Treatment
                nb = GetNumberBooksBySerieTreatment(idSerie, conn);
            }
            else
            {
                using (conn = new SQLiteConnection(Connection.CatalogConnection))
                {
                    conn.Open();

                    // Treatment
                    nb = GetNumberBooksBySerieTreatment(idSerie, conn);
                }
            }

            return nb;
        }

        /// <summary>
        /// Gets the serie by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>The serie or null</returns>
        public Serie GetSerieById(Int64 id, SQLiteConnection conn = null)
        {
            Serie serie;

            if (conn != null)
            {
                // Treatment
                serie = GetSerieByIdTreatment(id, conn);
            }
            else
            {
                using (conn = new SQLiteConnection(Connection.CatalogConnection))
                {
                    conn.Open();

                    // Treatment
                    serie = GetSerieByIdTreatment(id, conn);
                }
            }

            return serie;
        }

        #endregion

        #region private functions

        /// <summary>
        /// Gets the number of books by serie (Main treatment).
        /// </summary>
        /// <param name="idSerie">The id of the serie.</param>
        /// <param name="conn">The connection to the database (opened).</param>
        /// <returns>The number of books in the serie.</returns>
        private static int GetNumberBooksBySerieTreatment(Int64 idSerie, SQLiteConnection conn)
        {
            var nb = 0;

            var query = "SELECT count(id) FROM books_series_link WHERE series=" + idSerie;
            var cmd = new SQLiteCommand(query, conn);

            var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                nb = reader.GetInt32(0);
            }

            // Clear the memory
            reader.Dispose();
            cmd.Dispose();

            return nb;
        }

        /// <summary>
        /// Gets the serie by id (Main treatment).
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="conn">The connection to the database (opened).</param>
        /// <returns>The serie or null</returns>
        private static Serie GetSerieByIdTreatment(Int64 id, SQLiteConnection conn)
        {
            Serie serie = null;

            var query = "SELECT name FROM series WHERE id=" + id;
            var cmd = new SQLiteCommand(query, conn);

            // Construction of the serie
            var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                // New serie
                serie = new Serie {Id = id, Name = reader.GetString(0)};
            }

            // Clear the memory
            reader.Dispose();
            cmd.Dispose();

            // Return null or the serie
            return serie;
        }

        #endregion

        
    }
}
